﻿create table sys_Permission
(
   Id int primary key,
   Name varchar(20) not null
);

create table sys_Role
(
   Id serial primary key,
   Name varchar(20) not null
);

create table sys_RolePermission
(
   RoleId int not null references sys_Role(Id),
   PermissionId int not null references sys_Permission(Id),
   primary key (RoleId, PermissionId)
);

create table sys_User
(
   Id serial primary key,
   LoginName varchar(20) not null,
   Password varchar(200) not null,
   Salt varchar(20) not null,
   LastIPAddress varchar(50),   
   CreationTime timestamptz not null default now(),
   UpdatedTime timestamptz not null default now(),
   State int not null default 1,
   LastLoginTime timestamptz
);

create table sys_UserInfo
(
   UserId int primary key references sys_User(Id),
   UserName varchar(100) not null,
   Sex varchar(10) not null,   
   WorkEmail varchar(200) not null,
   Mobile varchar(20) not null,
   IDCard varchar(50) not null,
   Tel varchar(20),
   Birthday timestamptz,
   OtherEmail varchar(200)
);

create table sys_UserRole
(
   UserId int references sys_User(Id),
   RoleId int references sys_Role(Id),
   primary key(UserId, RoleId)
);

create table sys_Position
(
   Id serial primary key,
   Name varchar(50) not null
);

create table sys_Org
(
   Id serial primary key,
   Name varchar(50) not null,
   ParentOrgId int references sys_Org(Id),
   State int not null default 1
);

create table sys_UserOrgPosition
(
   UserId int not null references sys_User(Id),
   PositionId int not null references sys_Position(Id),
   OrgId int not null references sys_Org(Id),
   ParentUserId int null references sys_User(Id),
   primary key(UserId,PositionId,OrgId)
);

create table sys_Application
(
   Id int primary key,
   AppGuid varchar(50) not null,
   AppName varchar(50) not null
);

create table sys_Module
(
   Id serial primary key,
   AppId int not null references sys_Application(Id),
   Name varchar(50) not null,
   Url varchar(250)
);

create table sys_Menu
(
   Id serial primary key,
   ModuleId int not null references sys_Module(Id),
   Name varchar(50) not null,
   Url varchar(250),
   ParentMenuId int
);

create table sys_RoleMenu
(
   RoleId int not null references sys_Role(Id),
   MenuId int not null references sys_Menu(Id),
   primary key (RoleId, MenuId)
);


-- 数据
insert into sys_Permission(Id, Name) values(1,'读取全部'),(2,'读取部门'),(3,'读取自己'),(4,'写入数据');
insert into sys_Role(Name) values('超级管理员'), ('管理员'), ('普通用户');
insert into sys_RolePermission(RoleId, PermissionId) values(1,1),(1,4),(2,1),(2,4);
insert into sys_User(LoginName,Password,Salt,LastIPAddress) values('admin', '', '', '');
insert into sys_UserInfo(UserId, UserName,Sex,WorkEmail,Mobile,IDCard,Tel) values(1, '超级管理员', '男','','','','');
insert into sys_UserRole(UserId, RoleId) values(1,1);
insert into sys_Application(Id, AppGuid, AppName) values(1, 'd63302a9-e360-4d7e-a78e-87a439cb5685', 'WEB');
insert into sys_Module(AppId, Name) values(1, '后台管理'), (1, '工作流管理');
insert into sys_Menu(ModuleId, Name, Url) values(1, '用户管理', '/Admin/Home/User'),(1, '角色管理', '/Admin/Home/Role'),(1, '职位管理', '/Admin/Home/Position'),(1, '组织架构管理', '/Admin/Home/Org'),(1, '模块管理', '/Admin/Home/Module'),(1, '菜单管理', '/Admin/Home/Menu');
insert into sys_RoleMenu(RoleId, MenuId) values(2,1),(2,2),(2,3),(2,4),(2,5),(2,6);
